package com.fengpanlong.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.fengpanlong.entity.Employee;
import com.fengpanlong.entity.Project;
import com.fengpanlong.util.DBHelp;
import com.fengpanlong.util.Pager;
import com.fengpanlong.util.RowMapper;

public class ProjectDao {
	private DBHelp<Project> db = new DBHelp<Project>();
	
	private class ProjectRowMapper implements RowMapper<Project> {

		public Project mapperRow(ResultSet rs) throws SQLException {
			Project p = new Project();
			p.setId(rs.getString("id"));
			p.setName(rs.getString("name"));
			p.setDesc(rs.getString("desc"));
			p.setCreatetime(rs.getString("createtime"));
			return p;
		}
		
	}
	/**
	 * 保存项目
	 * @param p 项目对象
	 * @param manager 创建人对象
	 * @param employeeList 成员对象列表
	 */
	public void saveProject(Project p, Employee manager,
			List<Employee> employeeList) {
		String sql = "insert into t_project(id,name,`desc`,createtime) values(?,?,?,?)";
		db.executeSQL(sql, p.getId(),p.getName(),p.getDesc(),p.getCreatetime());
		sql = "insert into t_employee_project(employeeid,projectid,role) values(?,?,?)";
		db.executeSQL(sql, manager.getId(),p.getId(),"manager");
		for(Employee e : employeeList ) {
			sql = "insert into t_employee_project(employeeid,projectid,role) values(?,?,?)";
			db.executeSQL(sql, e.getId(),p.getId(),"employee");
		}
	}
	/**
	 * 通过项目名字查找项目
	 * @param name 项目名字
	 * @return 项目对象
	 */
	public Project findByName(String name) {
		String sql = "select id,name,`desc`,createtime from t_project where name = ?";
		return db.executeQueryForObject(sql, new ProjectRowMapper(), name);
	}
	/**
	 * 分页显示查找
	 * @param num
	 * @param name 
	 * @return
	 */
	public Pager<Project> findPagerOfProjectsByEmployeeId(int num, String employeeId) {
		int totalCount = getProjectCount();
		if(totalCount == 0) {
			return null;
		}
		Pager<Project> pager = new Pager<Project>(getProjectCount(),num);
		String sql = "select id,name,`desc`,createtime from t_project where id in (select projectid from t_employee_project where employeeid = ?) order by createtime desc limit ?,?";
		List<Project> result = db.executeQueryForList(sql, new ProjectRowMapper(),employeeId,pager.getStartIndex(),pager.getPageSize());
		pager.setResult(result);
		return pager;
	}
	/**
	 * 查找项目总数
	 * @return 项目总数
	 */
	private int getProjectCount() {
		String sql = "select count(*) from t_project";
		return db.executeQueryForCount(sql);
	}
	/**
	 * 通过项目id查找项目
	 * @param projectId 项目id
	 * @return 项目对象
	 */
	public Project findById(String projectId) {
		String sql = "select id,name,`desc`,createtime from t_project where id = ?";
		return db.executeQueryForObject(sql, new ProjectRowMapper(), projectId);
	}
}
